package com.scalar.chiptrack.engineering.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.engineering.LotYieldInfo;
import com.scalar.chiptrack.engineering.CummulativeYieldInfo;

import java.sql.*;
import java.util.HashMap;
import java.util.ArrayList;

/**
 * User: prasad
 * Date: Mar 3, 2008
 */
public class CummYieldDAO
{
    public static HashMap loadLotYieldDetails( DBTransaction dbTransaction, int productId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        HashMap lotYieldMap     = new HashMap();
        com.scalar.chiptrack.engineering.LotYieldInfo lotYieldInfo = null;

        int i = 0;
        String marking          = "";
        String assmLotNumber    = "";
        String tester           = "";
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            //pstmt = conn.prepareStatement ( "{ CALL sp_lot_history_by_lot_number( ? ) }" );
            pstmt = conn.prepareStatement ( "{ CALL sp_lot_yield_by_lot_number( ? ) }" );

            pstmt.setInt( 1, productId );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                lotYieldInfo = new LotYieldInfo();

                lotYieldInfo.setWaferLotNo( rset.getString( "Wafer_Lot_No" ) );
                lotYieldInfo.setFabOutWafer( rset.getInt( "Fab_out_Wafers" ) );
                lotYieldInfo.setDieRev( rset.getFloat( "DIE_REV" ) );
                /*lotYieldInfo.setWaferOutDate( StringUtils.formatDateToString( rset.getDate( "WAFER_OUTDATE" ), "MM/dd/yyyy") );
                lotYieldInfo.setWaferSortDate ( StringUtils.formatDateToString( rset.getDate( "WAFERSORT_DATE" ), "MM/dd/yyyy") );*/
                lotYieldInfo.setWaferOutDate( rset.getString( "WAFER_OUTDATE" ) );
                lotYieldInfo.setWaferSortDate ( rset.getString( "WAFERSORT_DATE" ) );
                lotYieldInfo.setWaferSortTestPgm ( rset.getString( "WAFERSORT_TEST_PGM" ) );
                lotYieldInfo.setTotalGoodDieQty ( rset.getInt( "TOTAL_GOOD_DIE_QTY" ) );
                lotYieldInfo.setWaferSortYield( rset.getFloat( "WAFERSORT_YILED" ) );
                lotYieldInfo.setMarking ( rset.getString( "MARKING" ) );
                lotYieldInfo.setAssemblyLotNo ( rset.getString( "ASSEMBLY_LOT_NOS" ) );
                lotYieldInfo.setAssemblyInQty ( rset.getInt( "ASSEMBLY_IN_QTY" ) );
                lotYieldInfo.setAssemblyYield ( rset.getFloat( "ASSEMBLY_YIELD" ) );
                lotYieldInfo.setTestQtyIn ( rset.getInt( "TEST_QTY_IN" ) );
                lotYieldInfo.setTestDateIn ( StringUtils.formatDateToString( rset.getDate( "TEST_DATE_IN" ), "MM/dd/yyyy") );
                lotYieldInfo.setBin1 ( rset.getInt( "BIN1" ) );
                lotYieldInfo.setBin2 ( rset.getInt( "BIN2" ) );
                lotYieldInfo.setBin3 ( rset.getInt( "BIN3" ) );
                lotYieldInfo.setBin4 ( rset.getInt( "BIN4" ) );
                lotYieldInfo.setBin5 ( rset.getInt( "BIN5" ) );
                lotYieldInfo.setGoodUnits ( rset.getInt( "GOOD_UNITS" ) );
                lotYieldInfo.setUnCountUnits ( rset.getInt( "UNCONUT_UNITS" ) );
                lotYieldInfo.setTestYield ( rset.getFloat( "TEST_YIELD" ) );
                lotYieldInfo.setTester ( rset.getString( "TESTER" ) );
                lotYieldInfo.setTestPgm ( rset.getString( "TEST_PGM" ) );
                lotYieldInfo.setScanOutQty ( rset.getInt( "SCAN_OUTQTY" ) );
                lotYieldInfo.setScanYield ( rset.getFloat( "SCAN_YIELD" ) );
                lotYieldInfo.setCummYield( rset.getFloat( "CUM_YIELD" ) );

                if( lotYieldInfo.getMarking() != null && lotYieldInfo.getMarking().trim().length() > 0 )
                {
                    marking = lotYieldInfo.getMarking().trim();
                    if( ",".equalsIgnoreCase( marking.substring( marking.length() - 1, marking.length() ) ) )
                    {
                        marking = marking.substring( 0, marking.length() - 1 );
                    }
                    lotYieldInfo.setMarking( marking.replaceAll( "\\\\n", "\012") );
                }

                if( lotYieldInfo.getAssemblyLotNo() != null && lotYieldInfo.getAssemblyLotNo().trim().length() > 0 )
                {
                    assmLotNumber = lotYieldInfo.getAssemblyLotNo().trim();
                    if( ",".equalsIgnoreCase( assmLotNumber.substring( assmLotNumber.length() - 1, assmLotNumber.length() ) ) )
                    {
                        assmLotNumber = assmLotNumber.substring( 0, assmLotNumber.length() - 1 );
                    }
                    lotYieldInfo.setAssemblyLotNo( assmLotNumber );
                }

                if( lotYieldInfo.getTester() != null && lotYieldInfo.getTester().trim().length() > 0 )
                {
                    tester = lotYieldInfo.getTester().trim();
                    if( ",".equalsIgnoreCase( tester.substring( tester.length() - 1, tester.length() ) ) )
                    {
                        tester = tester.substring( 0, tester.length() - 1 );
                    }
                    lotYieldInfo.setTester( tester );
                }

                lotYieldMap.put( lotYieldInfo.getWaferLotNo(), lotYieldInfo );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not load LotQty Discrepancy Records from database", e);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return lotYieldMap;
    }

    public static void loadInformation( DBTransaction dbTransaction, int productId ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        ResultSetMetaData resultSetMetaData = null;

        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "{ CALL sp_lot_history_by_lot_number( ? ) }" );

            pstmt.setInt( 1, productId );
            rset = pstmt.executeQuery();

            resultSetMetaData = rset.getMetaData();

            int numColumns = resultSetMetaData.getColumnCount();
            for (int i=1; i<(numColumns+1); i++)
            {
                String columnName = resultSetMetaData.getColumnName(i);
                String columnTypeName = resultSetMetaData.getColumnTypeName(i);
                int columnPrecision = resultSetMetaData.getPrecision(i);
                int columnScale = resultSetMetaData.getScale(i);
                //System.out.println( columnName + ": " +  columnTypeName + " - " + " Precision: " + columnPrecision + ", " + " Scale: " + columnScale );
                System.out.println( columnName + "( ) ( ) ");
            }

        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not load LotQty Discrepancy Records from database", e);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (resultSetMetaData != null)
            {
                try { resultSetMetaData = null ; } catch (Exception ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

    }

    public static void updateUploadInformation( int baseProductId ) throws ScalarException
    {
        Connection conn 			= null;
        PreparedStatement pstmt 	= null;
        ResultSet rset 			    = null;
        boolean success 			= false;
        DBTransaction dbTransaction = null;
        boolean isExists            = false;

        try
        {
            dbTransaction = DBTransaction.getInstance();
            if( dbTransaction != null )
            {
                dbTransaction.begin();
            }

            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT COUNT(*) AS COUNT FROM CUMMULATIVE_YIELD_REPORT_UPLOAD_LOG_TABLE WHERE PRODUCT_ID = ?" );

            pstmt.setInt( 1, baseProductId );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                int count = rset.getInt ( "COUNT" );
                if ( count > 0 )
                {
                    isExists = true;
                }
            }

            if( rset != null )
            {
                rset.close();
                rset = null;
            }
            if( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            if( isExists )
            {
                pstmt = conn.prepareStatement ( "UPDATE CUMMULATIVE_YIELD_REPORT_UPLOAD_LOG_TABLE SET UPLOAD_TIMESTAMP = CURRENT_TIMESTAMP WHERE PRODUCT_ID = ?" );
            }
            else
            {
                pstmt = conn.prepareStatement ( "INSERT INTO CUMMULATIVE_YIELD_REPORT_UPLOAD_LOG_TABLE VALUES( ?, CURRENT_TIMESTAMP, NULL )" );
            }
            pstmt.setInt( 1, baseProductId );

            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update Upload Timestamp into the database");
        }
        finally
        {
            try {
                if(dbTransaction == null){
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (success == true) {
                            dbTransaction.commit();
                        }else{
                        dbTransaction.rollback();
                        }
                    }
                    catch (DBXException e)
                    {
                        e.printStackTrace();
                    }

                }
            }
            catch (SQLException ignore) {}

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void updateYieldFileProcessInformation( int baseProductId ) throws ScalarException
    {
        Connection conn 			= null;
        PreparedStatement pstmt 	= null;
        ResultSet rset 			    = null;
        boolean success 			= false;
        DBTransaction dbTransaction = null;
        boolean isExists            = false;

        try
        {
            dbTransaction = DBTransaction.getInstance();
            if( dbTransaction != null )
            {
                dbTransaction.begin();
            }
            
            if( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT COUNT(*) AS COUNT FROM CUMMULATIVE_YIELD_REPORT_UPLOAD_LOG_TABLE WHERE PRODUCT_ID = ?" );

            pstmt.setInt( 1, baseProductId );
            rset = pstmt.executeQuery();

            if ( rset.next() )
            {
                int count = rset.getInt ( "COUNT" );
                if ( count > 0 )
                {
                    isExists = true;
                }
            }

            if( rset != null )
            {
                rset.close();
                rset = null;
            }
            if( pstmt != null )
            {
                pstmt.close();
                pstmt = null;
            }

            if( isExists )
            {
                pstmt = conn.prepareStatement ( "UPDATE CUMMULATIVE_YIELD_REPORT_UPLOAD_LOG_TABLE SET PROCESS_TIMESTAMP = CURRENT_TIMESTAMP WHERE PRODUCT_ID = ?" );
            }
            else
            {
                pstmt = conn.prepareStatement ( "INSERT INTO CUMMULATIVE_YIELD_REPORT_UPLOAD_LOG_TABLE VALUES( ?, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP )" );
            }
            pstmt.setInt( 1, baseProductId );

            pstmt.executeUpdate();

            success = true;
        }
        catch( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException ("Could not Update Processed Timestamp into the database");
        }
        finally
        {
            try {
                if(dbTransaction == null){
                    if (conn != null) {
                        if (success == true) {
                            conn.commit();
                        }
                        else {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    try
                    {
                        if (success == true) {
                            dbTransaction.commit();
                        }else{
                        dbTransaction.rollback();
                        }
                    }
                    catch (DBXException e)
                    {
                        e.printStackTrace();
                    }

                }
            }
            catch (SQLException ignore) {}

            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore){}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore){}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore){}
            }
        }
    }

    public static void loadYieldFileUploadAndProcessedDetails( DBTransaction dbTransaction, int baseProductId, CummulativeYieldInfo yieldInfo ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        try
        {
            if( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT UPLOAD_TIMESTAMP, PROCESS_TIMESTAMP FROM CUMMULATIVE_YIELD_REPORT_UPLOAD_LOG_TABLE WHERE PRODUCT_ID = ?" );

            pstmt.setInt( 1, baseProductId );
            rset = pstmt.executeQuery();

            if( rset.next() )
            {
                //yieldInfo.setUploadDateTime     ( StringUtils.formatDateToString( rset.getDate( "UPLOAD_TIMESTAMP" ), "MM-dd-yyyy-HH-mm-ss") );
                //yieldInfo.setLastUpdatedDateTime( StringUtils.formatDateToString( rset.getDate( "PROCESS_TIMESTAMP" ), "MM-dd-yyyy-HH-mm-ss") );

                yieldInfo.setUploadDateTime     ( StringUtils.formatDateandTime( rset.getString( "UPLOAD_TIMESTAMP") ) );
                yieldInfo.setLastUpdatedDateTime( StringUtils.formatDateandTime( rset.getString( "PROCESS_TIMESTAMP" ) ) );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not load File Upload TimeStamp Information from database", e);
        }
        finally
        {
            if (rset != null)
            {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null)
            {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null)
            {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
    }

}
